# 1、类作业，统计镰刀和斧头的数量，要使用类方法
# class Tool():
#     count=0
#     tool_count = {}
#     def __init__(cls,name):
#         cls.name=name
#         Tool.count+=1
#         if name in Tool.tool_count:
#             Tool.tool_count[name]+=1
#         else:
#             Tool.tool_count[name]=1
#     @classmethod
#     def show_tool_count(cls):
#         return cls.count
#     @classmethod
#     def show_tool_counts(cls):
#         return cls.tool_count
# tool1=Tool('镰刀')
# tool2=Tool('斧头')
# tool3=Tool('镰刀')
# tool4=Tool('镰刀')
# tool_count = Tool.show_tool_count()
# tool_counts=Tool.show_tool_counts()
# print("工具数量:", tool_count)
# print("每个工具数量",tool_counts)


# 2、做一个学生点名系统，系统里支持：存入学生、更改学生、删除学生、点到学生姓名方法
# 存入学生（姓名、年龄、家庭地址、手机号）： 存入成功后，提示xxx存入成功
# 更改学生（姓名、年龄、家庭地址、手机号）：可以修改学生内容的任意一个字段
# 删除学生：删除的学生将不在展示
# 展示全部学生：可以看到全部的学生姓名
# 学生姓名：随机抽取任意一个存在的学生信息，然后打印：到！。举例： 刘闯到！
# 以上内容，需要连接数据库，每次的数据都需要增、删、改、查去做

# import pymysql
# class StudentTable:
#     def __init__(self, host, user, password, database, cursorclass=None):
#         self.host = host
#         self.user = user
#         self.password = password
#         self.database = database
#         self.cursorclass = cursorclass
#     def insert_student(self, name, age, address, tel):
#         db = pymysql.connect(host=self.host, user=self.user,
#                              password=self.password, database=self.database,
#                              cursorclass=self.cursorclass)
#         cursor = db.cursor()
#         sql = "insert into studentsystem (name, age, address, tel) VALUES (%s, %s, %s, %s)"
#         values = (name, age, address, tel)
#         cursor.execute(sql, values)
#         db.commit()
#         print(f"{name} 存入成功")
#     def update_student(self, name, field, new_value):
#         db = pymysql.connect(host=self.host, user=self.user,
#                              password=self.password, database=self.database,
#                              cursorclass=self.cursorclass)
#         cursor = db.cursor()
#         sql = f"update studentsystem set {field}=%s WHERE name=%s"
#         values = (new_value, name)
#         cursor.execute(sql, values)
#         if cursor.rowcount > 0:
#             db.commit()
#             print(f"更新{name}的{field}成功")
#         else:
#             print(f"未找到姓名为{name}的学生")
#     def delete_student(self,name):
#         db = pymysql.connect(host=self.host, user=self.user,
#                              password=self.password, database=self.database,
#                              cursorclass=self.cursorclass)
#         cursor = db.cursor()
#         sql = "delete from studentsystem where name=%s"
#         values = (name,)
#         cursor.execute(sql, values)
#         if cursor.rowcount > 0:
#             db.commit()
#             print(f"删除{name}成功")
#         else:
#             print(f"未找到姓名为{name}的学生")
#     def show_allstudent(self):
#         db = pymysql.connect(host=self.host, user=self.user,
#                             password=self.password, database=self.database,
#                             cursorclass=self.cursorclass)
#         cursor = db.cursor()
#         sql = "select name from studentsystem"
#         cursor.execute(sql)
#         students = cursor.fetchall()
#         print("全部学生姓名：")
#         for student in students:
#             print(student)
# StudentTable1 = StudentTable(host="182.92.193.163", user="root",
#                              password="991012", database="jyl",
#                              cursorclass=pymysql.cursors.DictCursor)
# StudentTable1.insert_student("张三", 18, "北京", "1234567890")
# StudentTable1.insert_student("刘备", 55, "北京", "1311111111")
# StudentTable1.update_student("张三", "age", 20)
# StudentTable1.delete_student("张三")
# StudentTable1.show_allstudent()


# 3、新建学生成绩管理系统，随机生成100个 学生信息插入到数据库：
# 学生信息组成：学号、学生姓名、英语成绩、数学成绩、语文成绩、班级号（班级号有：1809A，1809B，1810A，1810B）
# 成绩划分为：优秀，良好，中等，及格，不及格。良好比中等好。
# 优秀：90—100之间（包括90）
# 良好：80-90
# 中等：70-80
# 及格：60-70
# 不合格：分数低于60分。
# 成绩管理系统提供以下方法：
# 《1》 查询所有英语成绩为优秀的、良好的、中等、及格和不合格的学生姓名和班级号
# 《2》 查询1809A，1809B，1810A，1810B每个班级的数学成绩，并且是降序返回
# 《3》 查询1809A，1809B，1810A，1810B每个班级的总人数，并且由大到小排序返回
# 《4》 查询英语、数学、语文成绩不合格的学生姓名和对应的班级号，按照不合格的人数，由大到小排序

from faker import Faker
class ScoreManagementSystem:
    def connect(self):
        self.db = pymysql.connect(user='root', password='123456', host="47.104.65.165", database="test",
                                  cursorclass=pymysql.cursors.DictCursor)
        self.cursor = self.db.cursor()

    def close(self):
        self.cursor.close()
        self.db.close()
    def random_student_info(self):
        self.connect()
        try:
            self.cursor.execute("create table student1(id int primary key auto_increment,name varchar(30),"
                                "english int(30),math int(30),chinese int(30),class varchar(30) )")
            self.db.commit()
        except:
            pass
        faker = Faker(locale="zh_cn")
        for i in range(100):
            name = faker.name()
            score_math = random.randint(0,100)
            score_Chinese = random.randint(0,100)
            score_English = random.randint(0,100)
            class1 = ["1809A","1809B","1810A","1810B"]
            #print(f"insert into student1(name,english,math,chinese,class) values('{name}','{score_English}','{score_math}','{score_Chinese}','{random.choice(class1)}')")
            self.cursor.execute(f"insert into student1(name,english,math,chinese,class) values('{name}',{score_English},{score_math},{score_Chinese},'{random.choice(class1)}')")
            self.db.commit()
        self.close()
    # 查询所有英语成绩为优秀的、良好的、中等、及格和不合格的学生姓名和班级号
    # 优秀：90—100之间（包括90）
    # 良好：80-90
    # 中等：70-80
    # 及格：60-70
    # 不合格：分数低于60分
    def search_student_englishScore(self):
        self.connect()
        # 优秀：90—100之间（包括90）
        self.cursor.execute("select name from student1 where english >= 90 and english <=100")
        excellent = self.cursor.fetchall()

        #  良好：80-90
        self.cursor.execute("select name from student1 where english < 90 and english >= 80")
        good = self.cursor.fetchall()

        # 中等：70-80
        self.cursor.execute("select name from student1 where english >=70 and english < 80")
        medium = self.cursor.fetchall()

        # 及格：60-70
        self.cursor.execute("select name from student1 where english >=60 and english < 70")
        pass1 = self.cursor.fetchall()

        #不合格：分数低于60分
        self.cursor.execute("select name from student1 where english <60")
        unqualified = self.cursor.fetchall()

        self.close()
        return f"优秀：90—100之间（包括90）:{excellent} \n\n良好：80-90：{good} \n\n中等：70-80：{medium}  \n\n及格：60-70 ：{pass1} \n\n不合格：分数低于60分 ：{unqualified}\n\n"

    #查询1809A，1809B，1810A，1810B每个班级的数学成绩，并且是降序返回
    def Descending(self):
        self.connect()
        self.cursor.execute("select math,class from student1 where class='1809A' order by math desc")
        result1 = self.cursor.fetchall()

        self.cursor.execute("select math,class from student1 where class='1809B' order by math desc")
        result2 = self.cursor.fetchall()

        self.cursor.execute("select math,class from student1 where class='1810A' order by math desc")
        result3 = self.cursor.fetchall()

        self.cursor.execute("select math,class from student1 where class='1810B' order by math desc")
        result4 = self.cursor.fetchall()
        self.close()
        return f"1809A :{result1} \n\n1809B：{result2} \n\n1810A：{result3}  \n\n1810B ：{result4}\n\n"

    #《3》 查询1809A，1809B，1810A，1810B每个班级的总人数，并且由大到小排序返回
    def class_count(self):
        self.connect()
        self.cursor.execute("select count(*) as '班级总人数',class from student1  group by class order by count(*) desc;")
        result = self.cursor.fetchall()
        self.close()
        return result

    # 《4》 查询英语、数学、语文成绩不合格的学生姓名和对应的班级号，按照不合格的人数，由大到小排序
    def unqualified_sort(self):
        self.connect()
        self.cursor.execute("select name,class from student1 where class='1809A' and ( english < 60 and math <60 and chinese < 60)")
        result1 = self.cursor.fetchall()

        self.cursor.execute("select name,class from student1 where class='1809B' and ( english < 60 and math <60 and chinese < 60)")
        result2 = self.cursor.fetchall()

        self.cursor.execute("select name,class from student1 where class='1810A' and ( english < 60 and math <60 and chinese < 60)")
        result3 = self.cursor.fetchall()

        self.cursor.execute("select name,class from student1 where class='1810B' and ( english < 60 and math <60 and chinese < 60)")
        result4 = self.cursor.fetchall()
        dict1 = {"1809A":len(result1),"1809B":len(result2),"1810A":len(result3),"1810B":len(result4)}
        dict_new = list(dict1.items())
        for i in range(len(dict_new)):
            for j in range(len(dict_new) -1 - i):
                if dict_new[j][1] < dict_new[j + 1][1]:
                    dict_new[j], dict_new[j + 1] = dict_new[j + 1], dict_new[j]
        return dict(dict_new)



s1 = ScoreManagementSystem()
print(s1.unqualified_sort())










# 4、定义一个图书管理系统类，图书可以新增、修改、删除，要求新增、修改、删除的图书，都要出现在数据库中
# 新增：图书名、作者名称、新建年月日
# 修改：图书名、作者名称、新建年月日
# 删除：图书删掉，且不在展示
# 查看：全量查询、精确查询、模糊查询需要用户传入形参来控制返回结果

class BookManagerSystem:
    def connect(self):
        self.db = pymysql.connect(user='root', password='123456', host="47.104.65.165", database="test",
                                  cursorclass=pymysql.cursors.DictCursor)
        self.cursor = self.db.cursor()
    def close(self):
        self.cursor.close()
        self.db.close()

    def add_book(self,bookname,name,addtime):
        self.connect()
        try:
            self.cursor.execute("create table book(id int primary key auto_increment,bookname varchar(30),name varchar(30),addtime varchar(30))")
            self.db.commit()
        except:
            pass
        self.cursor.execute(f"insert into book(bookname,name,addtime) values('{bookname}','{name}','{addtime}')")
        self.db.commit()
        self.close()
        return 1
    def update_book(self,bookold_name,booknew_name,name,addtime):
        self.connect()
        self.cursor.execute(f"update book set bookname='{booknew_name}',name='{name}',addtime='{addtime}' where bookname='{bookold_name}'")
        self.db.commit()
        self.close()
        return 1

    def delete_book(self,bookname):
        self.connect()
        self.cursor.execute(f"delete from book where bookname='{bookname}'")
        self.db.commit()
        self.close()
        return 1

    # 全量查询、精确查询、模糊查询
    def search_bookname(self,type = None,bookname = None): # type : 1 全量   2 精确  3模糊
        self.connect()
        if type == 1 or type == "1":
            self.cursor.execute(f"select bookname from book")
            result = self.cursor.fetchall()
            return result
        elif type == 2 or type == "2":
            if bookname:
                self.cursor.execute(f"select bookname from book where bookname = '{bookname}'")
                result = self.cursor.fetchall()
                return result
            else:
                return 0
        elif type == 3 or type == "3":
            if bookname:
                self.cursor.execute(f"select bookname from book where bookname like '%{bookname}%'")
                result = self.cursor.fetchall()
                return result
            else:
                return 0


b1 = BookManagerSystem()
# print(b1.add_book("小天才", "范庆才", "2023-8-30"))
# print(b1.add_book("小天才1", "范庆才1", "2023-8-30"))
# print(b1.add_book("小天才2", "范庆才2", "2023-8-30"))
# print(b1.update_book("大天才", "小天才puls","范庆才", "2023-8-30"))
# print(b1.delete_book("小天才puls"))
# print(b1.search_bookname(1))
# print(b1.search_bookname(2,"小天才"))
# print(b1.search_bookname(3,"天才"))